﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WFDataBaseProject.Helpers;
using System.Activities;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data.OleDb;
using System.ComponentModel;
using System.Activities.Presentation.Metadata;
using System.Activities.Presentation.PropertyEditing;
using WFDataBaseProject.Controls;

namespace WFDataBaseProject
{
    //Note: very imprtant the sql parameter should have a size to avoid the Invalidoperation exception
    [Designer(typeof(UCExecuteStoredProcedureActivityDesigner))]
    public class ExecuteStoredProcedureActivity:CodeActivity, WFDataBaseProject.IExecuteDBActivity
    {

        public ExecuteStoredProcedureActivity()
        {
            AttributeTableBuilder builder = new AttributeTableBuilder();
            builder.AddCustomAttributes(typeof(ExecuteStoredProcedureActivity), "ConnectionString", new EditorAttribute(typeof(ConnectionStringEditor), typeof(DialogPropertyValueEditor)));
            builder.AddCustomAttributes(typeof(ExecuteStoredProcedureActivity), "Parameters", new EditorAttribute(typeof(StoredProcedureParametersEditor), typeof(DialogPropertyValueEditor)));
            MetadataStore.AddAttributeTable(builder.CreateTable());
        }
        
        
        #region Arguments
        [Category("Inputs")]
        [Description("The connection string to connect to the data base")]
        [RequiredArgument]
        public InArgument<string> ConnectionString { get; set; }
        [Category("Inputs")]
        [RequiredArgument]
        [Description("The connection type sql server,oledb,odbc,oracle or other")]
        public InArgument<TypeOfConnection> ConnectionType { get; set; }
        [Category("Inputs")]
        [RequiredArgument]
        [Description("The returned type whether it is a data table, a single record or just a none query execution")]
        public InArgument<TypeOfReturn> ReturnedType { get; set; }
        [Browsable(false)]
        public InArgument<SqlSourceLocation> SourceLocation { get; set; }
        [Category("Inputs")]
        [RequiredArgument]
        [Description("The name of the stored procedure to execute")]
        public InArgument<string> StoredProcedureName { get; set; }
        [Category("Inputs")]
        [Description("The parameters to be used within the stored procedure")]
        public InArgument<List<IDbDataParameter>> Parameters { get; set; }
        [Browsable(false)]
        public OutArgument<DataTable> ReturnedTable { get; set; }
        [Browsable(false)]
        public OutArgument<object> ReturnedScalar { get; set; }
        [Category("Outputs")]
        [Description("The parameters to be turned back by the stored procedure")]
        public OutArgument<object> OutParemeters { get; set; }

        #endregion

        #region private fields
        List<IDbDataParameter> outparameterlist;
        string connectionstring;
        TypeOfConnection connectiontype;
        TypeOfReturn returnedtype;
        SqlSourceLocation sourcelocation;
        string storedprocedurename;
        #endregion
   
        protected override void Execute(CodeActivityContext context)
        {
            connectionstring = ConnectionString.Get<string>(context);
            connectiontype = ConnectionType.Get<TypeOfConnection>(context);
            returnedtype = ReturnedType.Get<TypeOfReturn>(context);
            sourcelocation = SourceLocation.Get<SqlSourceLocation>(context);
            storedprocedurename = StoredProcedureName.Get<string>(context);


            switch (connectiontype)
            {
                case TypeOfConnection.OledbConnection:
                    break;
                case TypeOfConnection.SqlServerConnection:
                    {
                        SqlConnection sqlconnection = new SqlConnection(connectionstring);
                        try
                        {
                            sqlconnection.Open();
                            SqlCommand sqlcommand = new SqlCommand();
                            sqlcommand.Connection = sqlconnection;
                            sqlcommand.CommandType = CommandType.StoredProcedure;
                            sqlcommand.CommandText = storedprocedurename;
                            List<IDbDataParameter> parameters = Parameters.Get<List<IDbDataParameter>>(context);
                            if (parameters.Count!=0)
                            {
                                
                                foreach (IDbDataParameter item in parameters)
                                {
                                    SqlParameter param = (SqlParameter)item;
                                    sqlcommand.Parameters.Add(param);
                                    if (outparameterlist==null && (param.Direction == ParameterDirection.Output||
                                        param.Direction == ParameterDirection.InputOutput||
                                        param.Direction == ParameterDirection.ReturnValue) )
                                    {
                                        outparameterlist = new List<IDbDataParameter>();
                                    }
                                    if (param.Direction == ParameterDirection.Output||
                                        param.Direction == ParameterDirection.InputOutput||
                                        param.Direction == ParameterDirection.ReturnValue)
                                    {
                                        outparameterlist.Add(param);
                                    }   
                                }
                                if (outparameterlist!=null)
                                {
                                    OutParemeters.Set(context, outparameterlist);
                                }
                            }
                            switch (returnedtype)
                            {
                                case TypeOfReturn.Request:
                                     SqlDataReader reader = sqlcommand.ExecuteReader();
                                     DataTable table = new DataTable(Guid.NewGuid().ToString());
                                     table.Load(reader);
                                     ReturnedTable.Set(context, table);
                                    break;
                                case TypeOfReturn.Scalar:
                                    object retObj = sqlcommand.ExecuteScalar();
                                    ReturnedScalar.Set(context, retObj);
                                    break;
                                case TypeOfReturn.None:
                                    sqlcommand.ExecuteNonQuery();
                                    break;
                                default:
                                    break;
                            }  

                        }
                        catch (SqlException caught)
                        {
#if DEBUG
                            Debug.Print(caught.Message);
#endif    
                            
                        }
                        break;
                    }
                case TypeOfConnection.ODBCConnection:
                    break;
                case TypeOfConnection.OtherConnection:
                    break;
                default:
                    break;
            }

        }      
   }
}
